package com.lwj.utils.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

public class PoiXlsReader {
	private static final Logger logger = LoggerFactory.getLogger(PoiXlsReader.class);
	private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	private List<SheetData> sheetDataList=new ArrayList<>();
	private Integer columnNum;
	private Workbook workbook;
	public Integer getColumnNum() {
		return columnNum;
	}

	public void setColumnNum(Integer columnNum) {
		this.columnNum = columnNum;
	}

	public Workbook getWorkbook() {
		return workbook;
	}

	public void setWorkbook(Workbook workbook) {
		this.workbook = workbook;
	}
	public SimpleDateFormat getDateFormat() {
		return dateFormat;
	}

	public void setDateFormat(SimpleDateFormat dateFormat) {
		this.dateFormat = dateFormat;
	}
	public Workbook parseWorkbook(MultipartFile file){
		try(InputStream is = file.getInputStream();){
			String excelFile = file.getOriginalFilename();
			if (excelFile.endsWith(".xls")) {
				workbook =new HSSFWorkbook(is);
			} else if (excelFile.endsWith(".xlsx")) {
				workbook= new XSSFWorkbook(is);
			} else {
				throw new RuntimeException("解析excel报错");
			}
			return workbook;
		}catch (Exception e){
			logger.error("解析excel报错",e);
			throw new RuntimeException("解析excel报错");
		}
	}
	public Workbook parseWorkbook(String excelFile){
		try(InputStream is = new FileInputStream(excelFile);){
			if (excelFile.endsWith(".xls")) {
				workbook =new HSSFWorkbook(is);
			} else if (excelFile.endsWith(".xlsx")) {
				workbook= new XSSFWorkbook(is);
			} else {
				throw new RuntimeException("解析excel报错");
			}
			return workbook;
		}catch (Exception e){
			logger.error("解析excel报错",e);
			throw new RuntimeException("解析excel报错");
		}
	}
	/**
	 * 读取excel

	 * @return
	 */
	public void readExcel(MultipartFile file) {
		Workbook wb = parseWorkbook(file);
		int numberOfSheets = wb.getNumberOfSheets();
		for (int i = 0; i < numberOfSheets; i++) {
			SheetData sheetData = read(wb, i);
			this.sheetDataList.add(sheetData);
		}
	}
	/**
	 * 读取excel

	 * @return
	 */
	public SheetData readExcel(MultipartFile file, int sheetNo) {
		Workbook wb = parseWorkbook(file);
		return read(wb,sheetNo);
	}
	/**
	 * 读取excel
	 *
	 * @param excelFile
	 * @return
	 */
	public void readExcel(String excelFile) {
		Workbook wb = parseWorkbook(excelFile);
		int numberOfSheets = wb.getNumberOfSheets();
		for (int i = 0; i < numberOfSheets; i++) {
			SheetData sheetData = read(wb, i);
            this.sheetDataList.add(sheetData);
		}
	}
	/**
	 * 读取excel

	 * @param sheetNo   sheet序号, 从0开始
	 * @return
	 */
	public SheetData readExcel(String excelFile , int sheetNo) {
		Workbook wb = parseWorkbook(excelFile);
        return read(wb,sheetNo);
	}
	/**
	 * 读取excel
	 *
	 * @param sheetNo   sheet序号, 从0开始
	 * @return
	 */
	public SheetData read(Workbook wb, int sheetNo) {
		// 用来存放表中数据
		List<List<String>> list = new ArrayList<>();
		Sheet sheet = wb.getSheetAt(sheetNo);
		Row row=null;
		// 获取最大行数
		int rowNum = sheet.getLastRowNum();
		String cellData=null;
		for (int i = 0; i <= rowNum; i++) {
			List<String> cells = new ArrayList<String>();
			row = sheet.getRow(i);
			if (row != null) {
				int colnum = columnNum==null?row.getLastCellNum():columnNum;
				for (int j = 0; j < colnum; j++) {
					cellData = formatCellValue(row.getCell(j));
					cells.add(cellData);
				}
			}
			list.add(cells);
		}
		String sheetName = sheet.getSheetName();
		return new SheetData(sheetName,list);
	}

	/**
	 * 获取单个单元格数据
	 * 
	 * @param cell
	 * @return
	 * @author lizixiang ,2018-05-08
	 */
	private String formatCellValue(Cell cell) {
		if(cell==null){
			return "";
		}
		CellType cellType = cell.getCellType();
		String str = cellValue(cell,cellType);
		if(str==null){
			FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
			cellType = evaluator.evaluate(cell).getCellType();
			str=cellValue(cell,cellType);
		}
		return str;
	}
	public String cellValue(Cell cell,CellType cellType ){
		String cellValue =  null;
		if(cellType.equals(CellType.STRING)){
			cellValue= cell.getRichStringCellValue().getString().trim();
		}else if(cellType.equals(CellType.NUMERIC)){
			if (DateUtil.isCellDateFormatted(cell)) {//日期类型
				Date dateCellValue = cell.getDateCellValue();
				cellValue= dateFormat.format(dateCellValue);
			} else {//数值类型
				cellValue= String.valueOf(cell.getNumericCellValue());
			}
		}else if(cellType.equals(CellType.BOOLEAN)){
			cellValue=cell.getBooleanCellValue()==true?"TRUE":"FALSE";
		}else if(cellType.equals(CellType.BLANK)){
			cellValue="";
		}
		return cellValue;
	}


	public String cellValue(int num,List<String> list){
		if(list ==null || list.size()<=num){
			return "";
		}else {
			return list.get(num);
		}
	}

	public static void main(String[] args) {
		PoiXlsReader poiXlsReader = new PoiXlsReader();
		List<List<String>> data = poiXlsReader.readExcel("D:\\testabc.xlsx", 0).getData();
		for (int i = 0; i < data.size(); i++) {
			List<String> list = data.get(i);
			for (int j = 0; j < 30; j++) {
				String s = poiXlsReader.cellValue(j,list);
				System.out.println("第"+(i+1)+"行第"+(j+1)+"列："+s);
			}
		}
		System.out.println(data);

	}


}